package utils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;


public class JDBCUtil
{
	public static final DBConnection CONNECTION = new DBConnection();
	private static Statement STATEMENT = null;


	public static void registerDriver()
	{
		DBConnection.registerDriver();
		
	}
	
	public static void registerDriver(String driver_class_name)
	{
		DBConnection.registerDriver(driver_class_name);
	}
	
	public static void init(String db_url)
	{
		JDBCUtil.CONNECTION.init(db_url);
		JDBCUtil.STATEMENT = JDBCUtil.CONNECTION.getStatement();
	}
	
	public static void init(String db_url, String username, String password)
	{
		JDBCUtil.CONNECTION.init(db_url, username, password);
		JDBCUtil.STATEMENT = JDBCUtil.CONNECTION.getStatement();
	}
	
	
	public static void init(String host, String port, String database_name, String username, String password)
	{
		JDBCUtil.CONNECTION.init(host, port, database_name, username, password);
		JDBCUtil.STATEMENT = JDBCUtil.CONNECTION.getStatement();
	}
	
	public static void init(String sub_protocol, String host, String port, String database_name, String username, String password)
	{
		JDBCUtil.CONNECTION.init(sub_protocol, host, port, database_name,username, password);
		JDBCUtil.STATEMENT = JDBCUtil.CONNECTION.getStatement();
	}
	
	
	public static final PreparedStatement getPreparedStatement(String sql)
	{
		return JDBCUtil.CONNECTION.getPreparedStatement(sql);
	}
	
	public static final void closeConnection()
	{
		JDBCUtil.CONNECTION.close();
	}
	
	
	public static final void statementExecute(String sql)
	{
		if (sql == null || sql.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".statementExecute:\n\tsql is null or equals \"\"");
			return;
		}
		try
		{
			JDBCUtil.STATEMENT.execute(sql);
		}
		catch (SQLException e) {e.printStackTrace();}
	}
	
	
	/**
	 * @return 如果set不能够last()则返回null
	 * */
	public static final ResultSet statementExecuteQuery(String sql)
	{
		if (sql == null || sql.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".statementExecuteQuery:\n\tsql is null or equals \"\"");
			return null;
		}
		try
		{
			ResultSet set = JDBCUtil.STATEMENT.executeQuery(sql);
			if (set != null && set.last())
			{
				return set;
			}
		}
		catch (SQLException e) {e.printStackTrace();}
		return null;
	}
	
	
	private static final String getUpdateSQL(String table_name, HashMap<String, String>key_value)
	{
		/*
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".getUpdateSQL:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (key_value == null || key_value.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".getUpdateSQL:\n\tthe key_value is null or empty");
			return null;
		}
		*/
		
		StringBuilder sql_set = new StringBuilder();
		for (String key : key_value.keySet())
		{
			sql_set.append(key + "='" + key_value.get(key) + "',");
		}
		sql_set.deleteCharAt(sql_set.length() - 1);
		
		String sql = "update " + table_name + " set " + sql_set.toString() + ";";
		return sql;
	}
	
	/**
	 * select from table
	 * @return 返回ResultSet；若 columns_name 为空，则返回整个表的ResultSet
	 * <br>ResultSet 不为null则可用，内容为空会返回null
	 * */
	public static final ResultSet select(String table_name,String... columns_name)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".select:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (columns_name == null || columns_name.length == 0)
		{
			System.out.println(JDBCUtil.class.getName() + ".select:\n\tthe columns_name is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_columns_name = new StringBuilder();
		
		if (columns_name == null || columns_name.length == 0)
			sql_columns_name.append(" * ");
		else
		{
			for (String name : columns_name)
			{
				sql_columns_name.append(name + ",");
			}
			sql_columns_name.deleteCharAt(sql_columns_name.length() - 1);
		}
		
		String sql = "select " + sql_columns_name + " from " + table_name + ";";
		return JDBCUtil.statementExecuteQuery(sql);
	}
	
	
	/**
	 * select一行
	 * @param table_name 表名
	 * @param conditions key='value'
	 * @return 返回ResultSet,内容只有一行
	 * <br>ResultSet 不为null则可用，内容为空会返回null
	 * */
	public static final ResultSet selectRow(String table_name, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".selectRow:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(JDBCUtil.class.getName() + ".selectRow:\n\tthe conditions is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String condition : conditions)
		{
			sql_conditions.append(" and " + condition);
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select * from " + table_name + " where " + sql_conditions + " limit 1;";
		return JDBCUtil.statementExecuteQuery(sql);
	}
	
	
	/**
	 * select一行
	 * @return 返回ResultSet,内容只有一行
	 * <br>ResultSet 不为null则可用，内容为空会返回null
	 * */
	public static final ResultSet selectRow(String table_name, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".selectRow:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".selectRow:\n\tthe conditions is null or empty");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String key : conditions.keySet())
		{
			sql_conditions.append(" and " + key + "='" + conditions.get(key) + "'");
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select * from " + table_name + " where " + sql_conditions + " limit 1;";
		
		return JDBCUtil.statementExecuteQuery(sql);
	}
	
	/**
	 * select某列某行的值
	 * @param conditions key='value'
	 * @return 返回value
	 * */
	public static final Object selectValue(String table_name, String column_name, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".selectValue:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(JDBCUtil.class.getName() + ".selectValue:\n\tthe conditions is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String condition : conditions)
		{
			sql_conditions.append(" and " + condition);
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select " + column_name + " from " + table_name + " where " + sql_conditions + ";";
		ResultSet set = JDBCUtil.statementExecuteQuery(sql);
		if (set == null)
			return null;
		try
		{
			/*
			if (!set.next())
				return null;
			*/
			
			// 在
			// JDBCUtil.statementExecuteQuery(sql); 
			// 里头已经检查过并且 set.last() 了
			return set.getObject(0);
		}
		catch (SQLException e) {e.printStackTrace();}
		return null;
	}
	
	
	/**
	 * select某列某行的值
	 * @return 返回value
	 * */
	public static final Object selectValue(String table_name, String column_name, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".selectValue:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".selectValue:\n\tthe conditions is null or empty");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String key : conditions.keySet())
		{
			sql_conditions.append(" and " + key + "='" + conditions.get(key) + "'");
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select " + column_name + " from " + table_name + " where " + sql_conditions + ";";
		ResultSet set = JDBCUtil.statementExecuteQuery(sql);
		if (set == null)
			return null;
		try
		{
			/*
			if (!set.next())
				return null;
			*/
			
			// 在
			// JDBCUtil.statementExecuteQuery(sql); 
			// 里头已经检查过并且 set.last() 了
			
			return set.getObject(0);
		}
		catch (SQLException e) {e.printStackTrace();}
		return null;
	}
	
	
	/**
	 * count()
	 * 计数，计算某列的行数
	 * @return 返回计数；若 columns_name 为空，则返回整个表所有列的行数
	 * */
	public static final int countRow(String table_name, String column_name)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".countRow:\n\ttable_name is null or equals \"\"");
			return 0;
		}
		if (column_name == null || column_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".countRow:\n\tthe column_name is null or equals \"\"");
			return 0;
		}
		
		String sql = "select count("
				+ column_name
				+ ") as count from "
				+ table_name
				+ ";";
		
		ResultSet set = JDBCUtil.statementExecuteQuery(sql);
		if (set == null)
			return 0;
		try
		{
			//if(!set.next())
			
			// 在
			// JDBCUtil.statementExecuteQuery(sql); 
			// 里头已经检查过并且 set.last() 了
			return set.getInt(1);
		}
		catch (SQLException e) {e.printStackTrace();}
		return 0;
	}
	
	
	/**
	 * count()
	 * 计数，计算某列的行数
	 * @return 返回计数；若 columns_name 为空，则返回整个表所有列的行数
	 * */
	public static final Integer[] countRow(String table_name, String... columns_name)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".countRow:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (columns_name == null || columns_name.length == 0)
		{
			System.out.println(JDBCUtil.class.getName() + ".countRow:\n\tthe columns_name is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_columns_name = new StringBuilder();
		for (String name : columns_name)
		{
			sql_columns_name.append(name + ",");
		}
		sql_columns_name.deleteCharAt(sql_columns_name.length() - 1);
		
		String sql = "select count("
				+ sql_columns_name.toString()
				+ ") as count from "
				+ table_name
				+ ";";
		
		ResultSet set = JDBCUtil.statementExecuteQuery(sql);
		if (set == null)
			return null;
		
		ArrayList<Integer> list_count = new ArrayList<Integer>();
		try
		{
			// JDBCUtil.statementExecuteQuery(sql);
			// 已经调用过 set.last()
			while(!set.next())
			{
				list_count.add(set.getInt(1));
			}
		}
		catch (SQLException e) {e.printStackTrace();}
		
		if (list_count.size() == 0)
			return null;
		return (Integer[])list_count.toArray();
	}
	
	
	/**
	 * insert行
	 * 设置所有value
	 * */
	public static final void insert(String table_name, String... values)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".insert:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (values == null || values.length == 0)
		{
			System.out.println(JDBCUtil.class.getName() + ".insert:\n\tthe values is null or whose length is 0");
			return;
		}
		
		StringBuilder sql_values = new StringBuilder();
		for (String value : values)
		{
			sql_values.append("'" + value + "',");
		}
		sql_values.deleteCharAt(sql_values.length() - 1);
		
		String sql = "insert into "
				+ table_name
				+ " values("
				+ sql_values
				+ ");";
		
		JDBCUtil.statementExecute(sql);
	}
	
	
	/**
	 * insert行
	 * 设置部分value
	 * */
	public static final void insert(String table_name, HashMap<String, String> key_value)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".insert:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (key_value == null || key_value.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".insert:\n\tthe key_value is null or empty");
			return;
		}
		
		StringBuilder sql_columns_name = new StringBuilder();
		StringBuilder sql_values = new StringBuilder();
		
		for (String key : key_value.keySet())
		{
			sql_columns_name.append(key + ",");
			sql_values.append("'" + key_value.get(key) + "',");
		}
		sql_columns_name.deleteCharAt(sql_columns_name.length() - 1);
		sql_values.deleteCharAt(sql_values.length() - 1);
		
		String sql = "insert into "
				+ table_name
				+ " ("
				+ sql_columns_name.toString()
				+ ") values ("
				+ sql_values.toString()
				+ ");";
		
		JDBCUtil.statementExecute(sql);
	}
	
	/**
	 * @param conditions key='value'
	 * */
	public static final void update(String table_name, HashMap<String, String> key_value, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".update:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (key_value == null || key_value.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".update:\n\tthe key_value is null or empty");
			return;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(JDBCUtil.class.getName() + ".update:\n\tthe conditions is null or whose length is 0");
			return;
		}
		
		StringBuilder sql = new StringBuilder(JDBCUtil.getUpdateSQL(table_name, key_value));
		sql.deleteCharAt(sql.length() - 1);
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String condition : conditions)
		{
			sql_conditions.append(" and " + condition);
		}
		sql_conditions.delete(0, 4);
		sql.append(" where " + sql_conditions + ";");
	
		
		JDBCUtil.statementExecute(sql.toString());
	}
	
	
	/**
	 * update某行中的某些列
	 * */
	public static final void update(String table_name, HashMap<String, String> key_value, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".update:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (key_value == null || key_value.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".update:\n\tthe key_value is null or empty");
			return;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".update:\n\tthe conditions is null or empty");
			return;
		}
		
		StringBuilder sql = new StringBuilder(JDBCUtil.getUpdateSQL(table_name, key_value));
		sql.deleteCharAt(sql.length() - 1);
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String column_name : conditions.keySet())
		{
			sql_conditions.append(" and " + column_name + "='" + conditions.get(column_name) + "'");
		}
		sql_conditions.delete(0, 4);
		sql.append(" where " + sql_conditions + ";");
	
	
		JDBCUtil.statementExecute(sql.toString());
	}
	
	
	/**
	 * update某行中的一列
	 * */
	public static final void updateOneValue(String table_name, String column_name, String value, String condition)
	{
		String sql = "update "
				+ table_name
				+ " set "
				+ column_name
				+ "='"
				+ value
				+ "' where "
				+ condition
				+ ";";
		JDBCUtil.statementExecute(sql);
	}
	
	
	/**
	 * 删除表中的行(row)
	 * @param conditions key='value'
	 * */
	public static final void delete(String table_name, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".delete:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(JDBCUtil.class.getName() + ".delete:\n\tthe conditions is null or whose length is 0");
			return;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		
		for (String condition : conditions)
		{
			sql_conditions.append(" and " + condition);
		}
		sql_conditions.delete(0, 4);
		
		String sql = "delete from " + table_name + " where " + sql_conditions.toString() + ";";
		JDBCUtil.statementExecute(sql);
	}
	
	/**
	 * 删除表中的行(row)
	 * */
	public static final void delete(String table_name, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(JDBCUtil.class.getName() + ".delete:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(JDBCUtil.class.getName() + ".delete:\n\tthe conditions is null or empty");
			return;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		
		for (String key : conditions.keySet())
		{
			sql_conditions.append(" and " + key + "='" + conditions.get(key) + "'");
		}
		sql_conditions.delete(0, 4);
		
		String sql = "delete from " + table_name + " where " + sql_conditions.toString() + ";";
		JDBCUtil.statementExecute(sql);
	}
}






